﻿using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;

namespace Shuba.Data
{
    public class TopUpBulk
    {
        public TopUpBulk() { }

        public int GetRowNumberTopUpApproval(string update_by, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM ( 
                    SELECT * FROM [TBL_TOPUP_TMP] (NOLOCK) WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND [BATCH_ID] = @BATCH_ID AND STATUS = 'P'
                    AND ERR_LINE = 0)AS DATA";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberTopUpByValues(string value, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM( 
                    SELECT * FROM TBL_TOPUP_TMP (NOLOCK) WHERE BATCH_ID = @BATCH_ID AND IS_DELETED = '0' AND STATUS = 'R' AND ((CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%'))) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberTopUpReal(string value, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            
            DbCommand cmd;
            sql = @"SELECT count(0) AS jmlData FROM( 
                    SELECT TP.UNIT_CODE FROM TBL_TOPUP (NOLOCK) T
                    RIGHT OUTER JOIN TBL_TOPUP_TMP (NOLOCK) TP
                    ON T.CIF_NUMBER = TP.CIF_NUMBER
                    AND T.LOAN_NUMBER = TP.LOAN_NUMBER
                    WHERE TP.ERR_LINE = 0 AND TP.STATUS = 'A' AND TP.BATCH_ID = @BATCH_ID AND ((TP.CIF_NUMBER LIKE '%' + @PARAM + '%') 
                        OR (TP.DEBITOR_NAME LIKE '%' + @PARAM + '%'))) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public int GetRowNumberTopUpFailed(string value, string batch_id)
        {
            int jmlRow = 0;
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT count(*) AS jmlData FROM( 
                    SELECT * FROM [TBL_TOPUP_TMP] WHERE [BATCH_ID] = @BATCH_ID AND IS_DELETED = '0' AND ERR_LINE > 0 AND ((CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%'))) AS DATA";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                    jmlRow = (int)dr["jmlData"];
                dr.Close();
            }
            return jmlRow;
        }

        public List<TopUpBulkInfo> GetListTopUp(string value, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<TopUpBulkInfo> list = new List<TopUpBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY " + sortBy + " " + sortDir + ") AS Row, * " +
                  "FROM TBL_TOPUP_TMP (NOLOCK) WHERE BATCH_ID = @BATCH_ID AND IS_DELETED = '0' AND STATUS = 'R' AND ((CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%'))" +
                  ")AS LogWithRowNumbers " +
                   "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), dr.GetString(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetString(37), dr.GetInt32(38), dr.GetString(39), dr.GetString(40), dr.GetString(41),"",""));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpBulkInfo> GetListTopUpReal(string value, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<TopUpBulkInfo> list = new List<TopUpBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string table = "TP.";
            if (sortBy == "DOWNLOAD_DATE" || sortBy == "ASSIGNED_TO" || sortBy == "IS_ASSIGNED") table = "T.";
            string sql;

            DbCommand cmd;
            sql = "SELECT LogWithRowNumbers.* FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY " + table + sortBy + " " + sortDir + ") AS Row, TP.[CIF_NUMBER] " +
                  ",TP.[LOAN_NUMBER],TP.[UNIT_CODE],TP.[DEBITOR_NAME],TP.[PRODUCT_CODE] " +
                  ",TP.[CURRENT_OS],TP.[CURRENT_INSTALLMENT],TP.[OFFER_PLAFON],TP.[OFFER_INSTALLMENT] " +
                  ",TP.[OFFER_INTEREST],TP.[OFFER_TENOR],TP.[OFFER_FRESH_MONEY],TP.[PRIORITY],TP.[JENIS_USAHA] " +
                  ",TP.[KODYA_KAB_BUSINESS],TP.[KECAMATAN_BUSINESS],TP.[KELURAHAN_BUSINESS],TP.[ZIP_CODE_BUSINESS] " +
                  ",TP.[ADDRESS_BUSINESS],TP.[MOBILE_PHONE],TP.[OFFICE_PHONE],TP.[RESIDENTIAL_PHONE] " +
                  ",TP.[DATE_VISIT_PLAN],TP.[ENTRY_BY],TP.[ENTRY_DATE],TP.[UPDATE_BY],TP.[UPDATE_DATE] " +
                  ",ISNULL(T.[IS_DOWNLOADED], '') IS_DOWNLOADED, ISNULL(T.[DOWNLOAD_BY],'') DOWNLOAD_BY,ISNULL(T.[DOWNLOAD_DATE], '01-01-2009') DOWNLOAD_DATE,TP.[PROSPECT_TYPE] " +
                  ",TP.[DEBTOR_TYPE],TP.[CHANNELING],TP.[EKSTERNAL],TP.[REFERENSI] " +
                  ",ISNULL(T.[IS_ASSIGNED], '') IS_ASSIGNED, ISNULL(T.[ASSIGNED_TO], '') ASSIGNED_TO, ISNULL(T.[ASSIGNED_DATE], '01-01-2009') ASSIGNED_DATE,TP.[BATCH_ID],TP.[FLAG_FREEZE],TP.[ACT_TYPE] " +
                        " FROM TBL_TOPUP (NOLOCK) T " +
                " RIGHT OUTER JOIN TBL_TOPUP_TMP (NOLOCK) TP " +
                " ON T.CIF_NUMBER = TP.CIF_NUMBER " +
                " AND T.LOAN_NUMBER = TP.LOAN_NUMBER " +
                " WHERE TP.ERR_LINE = 0 AND TP.STATUS = 'A' AND TP.BATCH_ID = @BATCH_ID AND ((TP.CIF_NUMBER LIKE '%' + @PARAM + '%') OR (TP.DEBITOR_NAME LIKE '%' + @PARAM + '%'))" +
                  ") AS LogWithRowNumbers " +
                   "WHERE Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), dr.GetString(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetString(37), dr.GetDateTime(38), dr.GetString(39), dr.GetInt16(40), dr.GetString(41)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpBulkInfo> GetListApprovalTopUp(string update_by, string batch_id, int currPage, int dataPerPage, string sortBy, string sortDir)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<TopUpBulkInfo> list = new List<TopUpBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = "SELECT  * FROM ( " +
                        "SELECT ROW_NUMBER() OVER (ORDER BY " + sortBy + " " + sortDir + ") AS Row, * "+
                "FROM [TBL_TOPUP_TMP] (NOLOCK) WHERE RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '' AND BATCH_ID = @BATCH_ID AND STATUS = 'P' AND ERR_LINE = 0 " +
                ") AS LogWithRowNumbers " +
                   "WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), dr.GetString(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetString(37), dr.GetInt32(38), dr.GetString(39), dr.GetString(40), dr.GetString(41)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpBulkInfo> GetListTopupFailed(string value, string batch_id, int currPage, int dataPerPage)
        {
            int barisAwal = 1 + (currPage - 1) * dataPerPage;
            int barisAkhir = barisAwal + dataPerPage - 1;
            List<TopUpBulkInfo> list = new List<TopUpBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT  * FROM ( 
                        SELECT ROW_NUMBER() OVER (ORDER BY UPDATE_DATE DESC) AS Row, * FROM( 
                        SELECT [BATCH_ID],[CIF_NUMBER],[LOAN_NUMBER],[UNIT_CODE],[DEBITOR_NAME],[PRODUCT_CODE]
                            ,[CURRENT_OS],[CURRENT_INSTALLMENT],[OFFER_PLAFON],[OFFER_INSTALLMENT],[OFFER_INTEREST],[OFFER_TENOR]
                            ,[OFFER_FRESH_MONEY],[PRIORITY],[JENIS_USAHA],[KODYA_KAB_BUSINESS],[KECAMATAN_BUSINESS],[KELURAHAN_BUSINESS]
                            ,[ZIP_CODE_BUSINESS],[ADDRESS_BUSINESS],[MOBILE_PHONE],[OFFICE_PHONE],[RESIDENTIAL_PHONE],[DATE_VISIT_PLAN]
                            ,[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[PROSPECT_TYPE],[DEBTOR_TYPE] 
                            ,[CHANNELING],[EKSTERNAL],[REFERENSI],[ERR_LINE],[ERR_DESC],[ACT_TYPE] 
                    FROM [TBL_TOPUP_TMP] WHERE [BATCH_ID] = @BATCH_ID AND IS_DELETED = '0' AND ERR_LINE > 0 AND ((CIF_NUMBER LIKE @PARAM) OR (DEBITOR_NAME LIKE @PARAM)))AS DATA 
                        ) AS LogWithRowNumbers 
                    WHERE  Row >= @BARIS_AWAL AND Row <= @BARIS_AKHIR";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, "%" + value + "%");
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@BARIS_AWAL", DbType.Int32, barisAwal);
            db.AddInParameter(cmd, "@BARIS_AKHIR", DbType.Int32, barisAkhir);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetDouble(10), dr.GetString(11), dr.GetInt16(12), dr.GetDouble(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetDateTime(28), dr.GetString(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetInt32(34), dr.GetString(35), dr.GetString(36)));
                }
                dr.Close();
            }
            return list;
        }

        public TopUpBulkInfo GetApprovalByID(string CIF_NUMBER, string LOAN_NUMBER)
        {
            TopUpBulkInfo ci = new TopUpBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP_TMP WHERE LTRIM(RTRIM(CIF_NUMBER)) = @CIF_NUMBER AND LTRIM(RTRIM(LOAN_NUMBER)) = @LOAN_NUMBER");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            cmd.CommandTimeout = 1800;

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new TopUpBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetInt32(37), dr.GetString(38), dr.GetString(39), dr.GetString(40));
                }
                dr.Close();
            }
            return ci;
        }

        public TopUpBulkInfo GetApprovalByIDandBatchId(string CIF_NUMBER, string LOAN_NUMBER, string BATCH_ID)
        {
            TopUpBulkInfo ci = new TopUpBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP_TMP WHERE LTRIM(RTRIM(CIF_NUMBER)) = @CIF_NUMBER AND LTRIM(RTRIM(LOAN_NUMBER)) = @LOAN_NUMBER AND LTRIM(RTRIM(BATCH_ID)) = @BATCH_ID");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, BATCH_ID);
            cmd.CommandTimeout = 1800;
            
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new TopUpBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetInt32(37), dr.GetString(38), dr.GetString(39), dr.GetString(40));
                }
                dr.Close();
            }
            return ci;
        }

        public int ApproveBulk(string CIF_NUMBER, string LOAN_NUMBER,string BATCH_ID, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("TOPUP_APPROVAL_BULK");

            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, BATCH_ID);
            cmd.CommandTimeout = 1800;

            return db.ExecuteNonQuery(cmd);
        }

        public Int32[] ApproveBulkAll(string batch_id, string status)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("TOPUP_APPROVAL_ALL");
            
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.AddInParameter(cmd, "@STATUS", DbType.String, status);
            db.AddOutParameter(cmd, "@ROWCOUNT_I", DbType.Int32, 50);
            db.AddOutParameter(cmd, "@ROWCOUNT_U", DbType.Int32, 50);
            db.AddOutParameter(cmd, "@ROWCOUNT_D", DbType.Int32, 50);
            cmd.CommandTimeout = 5600;
            db.ExecuteNonQuery(cmd);
            int insertCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_I"));
            int updateCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_U"));
            int deleteCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_D"));
            Int32[] countArr = new Int32[3] { insertCount, updateCount, deleteCount };
            return countArr;
        }

        public int Reject(string CIF_NUMBER, string LOAN_NUMBER, string BATCH_ID)
        {
            Database db = DBConnection.CreateDatabase();
            //DELETE FROM TBL_TOPUP_TMP
            DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_TOPUP_TMP SET STATUS = 'R', IS_DELETED = '0'
                            WHERE (LTRIM(RTRIM(CIF_NUMBER)) = @CIF_NUMBER ) AND (LTRIM(RTRIM(LOAN_NUMBER)) = @LOAN_NUMBER)
                                    AND (LTRIM(RTRIM(BATCH_ID)) = @BATCH_ID);");

            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, BATCH_ID);
            cmd.CommandTimeout = 1800;
            return db.ExecuteNonQuery(cmd);
        }

        public TopUpBulkInfo Get(string CIF_NUMBER, string LOAN_NUMBER)
        {
            TopUpBulkInfo ci = new TopUpBulkInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_TOPUP_TMP WHERE LTRIM(RTRIM(CIF_NUMBER)) = @CIF_NUMBER AND LTRIM(RTRIM(LOAN_NUMBER)) = @LOAN_NUMBER AND IS_DELETED = '0'");
            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new TopUpBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetInt32(37), dr.GetString(38), dr.GetString(39), dr.GetString(40));
                }
                dr.Close();
            }
            return ci;
        }

        public void delete(string batch_id)
        {
            Database db = DBConnection.CreateDatabase();

            DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_TOPUP_TMP SET IS_DELETED = '1' WHERE BATCH_ID = @BATCH_ID"); //DELETE * FROM TBL_TOPUP_TMP WHERE BATCH_ID = @BATCH_ID
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            db.ExecuteNonQuery(cmd);
        }

        private bool CharToBool(string source)
        {
            if (source == "0")
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public int deteleOnBulk(TopUpBulkInfo Info)
        {
            int status = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_TOPUP_TMP SET IS_DELETED = '1', STATUS = 'P', UPDATE_BY = @UPDATE_BY, UPDATE_DATE = @UPDATE_DATE, ACT_TYPE = @ACT_TYPE
                                WHERE LTRIM(RTRIM(CIF_NUMBER)) = @CIF_NUMBER AND LTRIM(RTRIM(LOAN_NUMBER)) = @LOAN_NUMBER");

                db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, Info.CIF_NUMBER);
                db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, Info.LOAN_NUMBER);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Info.UPDATE_BY);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Info.UPDATE_DATE);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, Info.ACT_TYPE);
                status = db.ExecuteNonQuery(cmd);
            }
            catch (Exception e) { }
            return status;
        }

        public List<TopUpBulkInfo> GetListTopUp(string value, string batch_id)
        {
            List<TopUpBulkInfo> list = new List<TopUpBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;
            DbCommand cmd;
            sql = @"SELECT TMP.[BATCH_ID],TMP.[CIF_NUMBER],TMP.[LOAN_NUMBER]
                          ,TMP.[UNIT_CODE],TBL_MS_BRANCH.BRANCH_NAME, TBL_MS_CLUSTER.CLUSTER_NAME
	                      ,TBL_MS_REGIONAL.REGIONAL_NAME,TMP.[DEBITOR_NAME],TMP.[PRODUCT_CODE]
                          ,TMP.[CURRENT_OS],TMP.[CURRENT_INSTALLMENT],TMP.[OFFER_PLAFON]
                          ,TMP.[OFFER_INSTALLMENT],TMP.[OFFER_INTEREST],TMP.[OFFER_TENOR]
                          ,TMP.[OFFER_FRESH_MONEY],TMP.[PRIORITY],TMP.[JENIS_USAHA]
                          ,TMP.[KODYA_KAB_BUSINESS],TMP.[KECAMATAN_BUSINESS],TMP.[KELURAHAN_BUSINESS]
                          ,TMP.[ZIP_CODE_BUSINESS],TMP.[ADDRESS_BUSINESS],TMP.[MOBILE_PHONE]
                          ,TMP.[OFFICE_PHONE],TMP.[RESIDENTIAL_PHONE],TMP.[DATE_VISIT_PLAN]
                          ,TMP.[ENTRY_BY],TMP.[ENTRY_DATE],TMP.[UPDATE_BY]
                          ,TMP.[UPDATE_DATE],TMP.[PROSPECT_TYPE],TMP.[DEBTOR_TYPE]
                          ,TMP.[CHANNELING],TMP.[EKSTERNAL],TMP.[REFERENSI]
                          ,TMP.[ERR_LINE],TMP.[ERR_DESC],TMP.[ACT_TYPE]
                    FROM [TBL_TOPUP_TMP] TMP LEFT JOIN 
	                    TBL_MS_BRANCH ON TMP.UNIT_CODE = TBL_MS_BRANCH.BRANCH_ID LEFT JOIN
                        TBL_MS_CLUSTER_BRANCH ON TBL_MS_BRANCH.BRANCH_ID = TBL_MS_CLUSTER_BRANCH.BRANCH_ID LEFT JOIN
                        TBL_MS_CLUSTER ON TBL_MS_CLUSTER_BRANCH.CLUSTER_ID = TBL_MS_CLUSTER.CLUSTER_ID LEFT JOIN
                        TBL_MS_REGION_CLUSTER ON TBL_MS_CLUSTER.CLUSTER_ID = TBL_MS_REGION_CLUSTER.CLUSTER_ID LEFT JOIN
                        TBL_MS_REGIONAL ON TBL_MS_REGION_CLUSTER.REGIONAL_ID = TBL_MS_REGIONAL.REGIONAL_ID
                   WHERE BATCH_ID = @BATCH_ID AND STATUS = 'R' 
                   AND IS_DELETED = '0' AND ((CIF_NUMBER LIKE '%' + @PARAM + '%') OR (DEBITOR_NAME LIKE '%' + @PARAM + '%'))";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 1800;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new TopUpBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetString(7), dr.GetString(8), dr.GetDouble(9), dr.GetDouble(10), dr.GetDouble(11), dr.GetDouble(12), dr.GetString(13), dr.GetInt16(14), dr.GetDouble(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetString(23), dr.GetString(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetDateTime(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetInt32(36), dr.GetString(37), dr.GetString(38)));
                }
                dr.Close();
            }
            return list;
        }

        public List<TopUpBulkInfo> GetListTopUpRealExcel(string value, string batch_id)
        {
            List<TopUpBulkInfo> list = new List<TopUpBulkInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql;

            /*
            DbCommand cmd;
            sql = @"SELECT T.*, TBL_MS_BRANCH.BRANCH_NAME, TBL_MS_CLUSTER.CLUSTER_NAME
	                      ,TBL_MS_REGIONAL.REGIONAL_NAME, TMP.ACT_TYPE
                    FROM [TBL_TOPUP] T LEFT JOIN 
                        TBL_TOPUP_TMP TMP ON T.CIF_NUMBER = TMP.CIF_NUMBER AND T.LOAN_NUMBER = TMP.LOAN_NUMBER LEFT JOIN
	                    TBL_MS_BRANCH ON T.UNIT_CODE = TBL_MS_BRANCH.BRANCH_ID LEFT JOIN
                        TBL_MS_CLUSTER_BRANCH ON TBL_MS_BRANCH.BRANCH_ID = TBL_MS_CLUSTER_BRANCH.BRANCH_ID LEFT JOIN
                        TBL_MS_CLUSTER ON TBL_MS_CLUSTER_BRANCH.CLUSTER_ID = TBL_MS_CLUSTER.CLUSTER_ID LEFT JOIN
                        TBL_MS_REGION_CLUSTER ON TBL_MS_CLUSTER.CLUSTER_ID = TBL_MS_REGION_CLUSTER.CLUSTER_ID LEFT JOIN
                        TBL_MS_REGIONAL ON TBL_MS_REGION_CLUSTER.REGIONAL_ID = TBL_MS_REGIONAL.REGIONAL_ID
                   WHERE T.BATCH_ID = @BATCH_ID AND ((T.CIF_NUMBER LIKE '%' + @PARAM + '%') OR (T.DEBITOR_NAME LIKE '%' + @PARAM + '%'))";
             */

            DbCommand cmd;
            sql = "SELECT LogWithRowNumbers.* FROM ( " +
                  "SELECT ROW_NUMBER() OVER (ORDER BY TP.[UPDATE_DATE] ASC) AS Row, TP.[CIF_NUMBER] " +
                  ",TP.[LOAN_NUMBER],TP.[UNIT_CODE],TP.[DEBITOR_NAME],TP.[PRODUCT_CODE] " +
                  ",TP.[CURRENT_OS],TP.[CURRENT_INSTALLMENT],TP.[OFFER_PLAFON],TP.[OFFER_INSTALLMENT] " +
                  ",TP.[OFFER_INTEREST],TP.[OFFER_TENOR],TP.[OFFER_FRESH_MONEY],TP.[PRIORITY],TP.[JENIS_USAHA] " +
                  ",TP.[KODYA_KAB_BUSINESS],TP.[KECAMATAN_BUSINESS],TP.[KELURAHAN_BUSINESS],TP.[ZIP_CODE_BUSINESS] " +
                  ",TP.[ADDRESS_BUSINESS],TP.[MOBILE_PHONE],TP.[OFFICE_PHONE],TP.[RESIDENTIAL_PHONE] " +
                  ",TP.[DATE_VISIT_PLAN],TP.[ENTRY_BY],TP.[ENTRY_DATE],TP.[UPDATE_BY],TP.[UPDATE_DATE] " +
                  ",ISNULL(T.[IS_DOWNLOADED], '') IS_DOWNLOADED, ISNULL(T.[DOWNLOAD_BY], '') DOWNLOAD_BY, ISNULL(T.[DOWNLOAD_DATE], '01-01-2009') DOWNLOAD_DATE,TP.[PROSPECT_TYPE] " +
                  ",TP.[DEBTOR_TYPE],TP.[CHANNELING],TP.[EKSTERNAL],TP.[REFERENSI] " +
                  ",ISNULL(T.[IS_ASSIGNED], '') IS_ASSIGNED, ISNULL(T.[ASSIGNED_TO], '') ASSIGNED_TO, ISNULL(T.[ASSIGNED_DATE], '01-01-2009') ASSIGNED_DATE,TP.[BATCH_ID],TP.[FLAG_FREEZE],TP.[ACT_TYPE] " +
                  ",TBL_MS_BRANCH.BRANCH_NAME, TBL_MS_CLUSTER.CLUSTER_NAME,TBL_MS_REGIONAL.REGIONAL_NAME " +
                        "FROM TBL_TOPUP_TMP TP " +
                " LEFT OUTER JOIN TBL_TOPUP T " +
                " ON T.CIF_NUMBER = TP.CIF_NUMBER " +
                " AND T.LOAN_NUMBER = TP.LOAN_NUMBER " +
                " LEFT JOIN " +
                       " TBL_MS_BRANCH ON TP.UNIT_CODE = TBL_MS_BRANCH.BRANCH_ID LEFT JOIN " +
                       " TBL_MS_CLUSTER_BRANCH ON TBL_MS_BRANCH.BRANCH_ID = TBL_MS_CLUSTER_BRANCH.BRANCH_ID LEFT JOIN " +
                       " TBL_MS_CLUSTER ON TBL_MS_CLUSTER_BRANCH.CLUSTER_ID = TBL_MS_CLUSTER.CLUSTER_ID LEFT JOIN " +
                       " TBL_MS_REGION_CLUSTER ON TBL_MS_CLUSTER.CLUSTER_ID = TBL_MS_REGION_CLUSTER.CLUSTER_ID LEFT JOIN " +
                       " TBL_MS_REGIONAL ON TBL_MS_REGION_CLUSTER.REGIONAL_ID = TBL_MS_REGIONAL.REGIONAL_ID " +
                " WHERE TP.ERR_LINE = 0 AND TP.STATUS = 'A' AND TP.BATCH_ID = @BATCH_ID AND ((TP.CIF_NUMBER LIKE '%' + @PARAM + '%') OR (TP.DEBITOR_NAME LIKE '%' + @PARAM + '%'))" +
                  ") AS LogWithRowNumbers ";

            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);
            db.AddInParameter(cmd, "@BATCH_ID", DbType.String, batch_id);
            cmd.CommandTimeout = 3600;
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    //list.Add(new TopUpBulkInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetDouble(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetString(9), dr.GetInt16(10), dr.GetDouble(11), dr.GetString(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetDateTime(22), dr.GetString(23), dr.GetDateTime(24), dr.GetString(25), dr.GetDateTime(26), dr.GetString(27), dr.GetString(28), dr.GetDateTime(29), dr.GetString(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetDateTime(37), dr.GetString(38), 0, dr.GetString(40), dr.GetString(41), dr.GetString(42), dr.GetString(43)));
                    list.Add(new TopUpBulkInfo(dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetDouble(6), dr.GetDouble(7), dr.GetDouble(8), dr.GetDouble(9), dr.GetString(10), dr.GetInt16(11), dr.GetDouble(12), dr.GetString(13), dr.GetString(14), dr.GetString(15), dr.GetString(16), dr.GetString(17), dr.GetString(18), dr.GetString(19), dr.GetString(20), dr.GetString(21), dr.GetString(22), dr.GetDateTime(23), dr.GetString(24), dr.GetDateTime(25), dr.GetString(26), dr.GetDateTime(27), dr.GetString(28), dr.GetString(29), dr.GetDateTime(30), dr.GetString(31), dr.GetString(32), dr.GetString(33), dr.GetString(34), dr.GetString(35), dr.GetString(36), dr.GetString(37), dr.GetDateTime(38), dr.GetString(39), dr.GetInt16(40), dr.GetString(41), dr.GetString(42), dr.GetString(43), dr.GetString(44)));
                }
                dr.Close();
            }
            return list;
        }
    }
}